package com.chinapost.service.impl;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.chinapost.dao.BaseDaoI;
import com.chinapost.excelUtil.DateUtil;
import com.chinapost.po.Tdept;
import com.chinapost.po.Tdqcd;
import com.chinapost.po.Tdqlkt;
import com.chinapost.po.Tdqobk;
import com.chinapost.po.Tjfsz;
import com.chinapost.po.Tjhrw;
import com.chinapost.po.Ttqsj;
import com.chinapost.po.Tuser;
import com.chinapost.po.Twd;
import com.chinapost.po.Tyxjl;
import com.chinapost.po.Tyxsj;
import com.chinapost.service.TqsjServiceI;
import com.chinapost.util.DoubleUtil;
import com.chinapost.vo.DataGrid;
import com.chinapost.vo.Tqsj;
import com.chinapost.vo.YxsjTj;

@Service("tqsjService")
public class TqsjServiceImpl implements TqsjServiceI {

	@Autowired
	private BaseDaoI<Ttqsj> tqsjDao;
	@Autowired
	private BaseDaoI<Tyxsj> yxsjDao;
	@Autowired
	private BaseDaoI<Tdept> deptDao;
	@Autowired
	private BaseDaoI<Tuser> userDao;
	@Autowired
	private BaseDaoI<Tdqcd> dqcdDao;
	@Autowired
	private BaseDaoI<Tdqlkt> dqlktDao;
	@Autowired
	private BaseDaoI<Tdqobk> dqobkDao;
	@Autowired
	private BaseDaoI<Tyxjl> yxjlDao;
	@Autowired
	private BaseDaoI<Tjfsz> jfszDao;
	@Autowired
	private BaseDaoI<Twd> wdDao;
	@Autowired
	private BaseDaoI<YxsjTj> tjDao;

	@Override
	public DataGrid datagrid(Tqsj tqsj) {
		DataGrid dg = new DataGrid();
		dg.setRows(listRows(find(tqsj)));
		dg.setTotal(total(tqsj));
		return dg;
	}

	private Long total(Tqsj tqsj) {
		String hql = "select count(*) from Ttqsj t where 1=1";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		return tqsjDao.count(hql, params);
	}

	private List<Ttqsj> find(Tqsj tqsj) {
		String hql = "from Ttqsj t where 1=1";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		if (tqsj.getOrder() != null && tqsj.getSort() != null) {
			hql += " order by " + tqsj.getSort() + " " + tqsj.getOrder();
		}
		return tqsjDao.find(hql, params, tqsj.getPage(), tqsj.getRows());
	}

	private List<Tqsj> listRows(List<Ttqsj> lt) {
		List<Tqsj> rows = new ArrayList<Tqsj>();
		if (lt != null && lt.size() > 0) {
			for (Ttqsj t : lt) {
				Tqsj sub = new Tqsj();
				BeanUtils.copyProperties(t, sub);
				sub.setCzh("**" + t.getCzh().substring(t.getCzh().length() - 7));
				if (!StringUtils.isEmpty(t.getCkh())){
					sub.setCkh("**" + t.getCkh().substring(t.getCkh().length() - 7));
				}
				sub.setDeptid(t.getTdept().getCid());
				sub.setDeptname(t.getTdept().getCname());
				sub.setKindid(t.getTkind().getCid());
				sub.setKindname(t.getTkind().getCname());
				sub.setUserid(t.getTuserByCuserid().getCid());
				sub.setUsername(t.getTuserByCuserid().getCname());
				rows.add(sub);
			}
		}
		return rows;
	}

	private String addWhere(Tqsj tqsj, String hql, Map<String, Object> params) {
		if (tqsj != null && tqsj.getCbh() != null && !tqsj.getCbh().trim().equals("")) {
			hql += " and t.cbh = :bh ";
			params.put("bh", tqsj.getCbh());
		}
		if (tqsj != null && tqsj.getDeptIds() != null && tqsj.getDeptIds().size() > 0) {
			hql += " and t.tdept.cid in(:deptids)";
			params.put("deptids", tqsj.getDeptIds());
		}
		if (tqsj != null && tqsj.getStartMonth() != null) {
			hql += " and cast(substring(t.ctqrq,1,6) as int) >= :startmonth";
			params.put("startmonth", tqsj.getStartMonth());
		}
		if (tqsj != null && tqsj.getEndMonth() != null) {
			hql += " and cast(substring(t.ctqrq,1,6) as int) <= :endmonth";
			params.put("endmonth", tqsj.getEndMonth());
		}
		if (tqsj != null && !StringUtils.isEmpty(tqsj.getCzh())) {
			hql += " and t.czh like :zh or t.ckh like :zh";
			params.put("zh", "%%" + tqsj.getCzh() + "%%");
		}
		return hql;
	}

	/**
	 * 根据条件查询出需要导出excel的数据
	 */
	@Override
	public List<Tqsj> excelData(Tqsj tqsj) {
		String hql = "from Ttqsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		hql += " order by t.cbh,t.csbyf";
		List<Ttqsj> tqsjs = tqsjDao.find(hql, params);
		return listRows(tqsjs);
	}

	/**
	 * 计算提前支取数据。 1、先从dqcd数据中找出所辖网点在当月发生了提前支取的账号集合 2、根据发生了提前支取的账号集合，在营销数据中查找该账号，如果找到则添加到提前支取表中 3、如果是部分提前支取，即有关联账号的，要循环找出最后状态为0的账号，防止一个月多次部提
	 */
	@Override
	public Integer addTqsjs(Tqsj tqsj) {
		Integer tqyf = tqsj.getCsbyf();
		Tuser createUser = userDao.get(Tuser.class, tqsj.getCreateuserid());//创建人，修改人
		Date d = new Date();
		Timestamp modifyDate = new Timestamp(d.getTime());//修改时间，创建时间
		int count = 0;
		String deptid = tqsj.getDeptid();
		List<String> wdids = listJghByDeptid(deptid);
		// 1.计算dqcd中的提前支取数据
		String dqcd_hql = " from Tdqcd t where t.czt=1 and cast(t.cxhr as int) < cast(t.cdqr as int) and substring(t.cxhr,1,6) =:tqyf and t.ckhj in(:wdids)";
		Map<String, Object> dqcd_params = new HashMap<String, Object>();
		dqcd_params.put("tqyf", tqyf.toString());
		dqcd_params.put("wdids", wdids);
		List<Tdqcd> tdqcds = dqcdDao.find(dqcd_hql, dqcd_params);// 获取所有提前支取的账号
		if (tdqcds != null && tdqcds.size() > 0) {
			// 根据账号和起息日查找营销数据，因为有3个月的多次上报，所以要判断起息日
			String yxsj_hql = " from Tyxsj t where t.czh =:zh and t.cqxr =:qxr and t.czt=:zt";
			Map<String, Object> yxsj_params = new HashMap<String, Object>();
			for (Tdqcd tdqcd : tdqcds) {
				yxsj_params.put("zh", tdqcd.getCzh());
				yxsj_params.put("qxr", tdqcd.getCqxr());
				yxsj_params.put("zt", "正常");//如果不是正常的说明已经提前支取或者部提了，不再重复计算
				Tyxsj tyxsj = yxsjDao.get(yxsj_hql, yxsj_params);// 查询营销数据中是否有该提支账号
				if (tyxsj != null) {
					// 如果存在该提支账号，判断是部提还是全部提前支取
					String glzh = tdqcd.getCglzh();
					if (!StringUtils.isEmpty(glzh)) {// 如果有关联账号，则可能属于部提
						Tdqcd dqcd = dqcdDao.get(Tdqcd.class, glzh);
						while (dqcd != null && !StringUtils.isEmpty(dqcd.getCglzh())) {// 做循环查找，防止一个月多次部提，找到最终的没有关联账号的
							glzh = dqcd.getCglzh();
							dqcd = dqcdDao.get(Tdqcd.class, glzh);
						}
						if (dqcd.getCzt() == 0) {// 最终账号不是销户状态，则是部提
							Ttqsj ttqsj = addBtTqsj(tqsj, tyxsj, 1, dqcd);//找出关联账号后添加到营销数据中
							if (ttqsj != null && !findTqsj(ttqsj)) {
								ttqsj.setCtqrq(tdqcd.getCxhr());//设置销户日为提前支取日期
								tyxsj.setCzt("部分提前支取");
								tyxsj.setCmodifydate(modifyDate);
								tyxsj.setTuserByCmodifyuserid(createUser);
								tqsjDao.save(ttqsj);
								count++;
							}

						} else {// 最终账号是销户状态，则是全部提前支取
							Ttqsj ttqsj = addAllTqsj(tqsj, tyxsj, 1, tdqcd);// 添加到提前支取表
							if (!findTqsj(ttqsj)) {
								tyxsj.setCzt("全部提前支取");
								tyxsj.setCmodifydate(modifyDate);
								tyxsj.setTuserByCmodifyuserid(createUser);
								tqsjDao.save(ttqsj);
								count++;
							}
						}
					} else {// 没有关联账号则是全部提前支取，写入提前支取表中
						Ttqsj ttqsj = addAllTqsj(tqsj, tyxsj, 1, tdqcd);// 添加到提前支取表
						if (!findTqsj(ttqsj)) {
							tyxsj.setCzt("全部提前支取");
							tyxsj.setCmodifydate(modifyDate);
							tyxsj.setTuserByCmodifyuserid(createUser);
							tqsjDao.save(ttqsj);
							count++;
						}
					}
				}
			}

		}
		// 2、计算绿卡通定期的提前支取数据
		String dqlkt_hql = " from Tdqlkt t where t.czt=1 and cast(t.cxhr as int) < cast(t.cdqr as int) and substring(t.cxhr,1,6) =:tqyf and t.ckhj in(:wdids)";
		Map<String, Object> dqlkt_params = new HashMap<String, Object>();
		dqlkt_params.put("tqyf", tqyf.toString());
		dqlkt_params.put("wdids", wdids);
		List<Tdqlkt> tdqlkts = dqlktDao.find(dqlkt_hql, dqlkt_params);// 获取所有绿卡通提前支取的账号
		if (tdqlkts != null && tdqlkts.size() > 0) {
			// 根据账号和起息日查找营销数据，因为有3个月的多次上报，所以要判断起息日
			String dqlkt_yxsj_hql = " from Tyxsj t where t.czh =:zh and t.cqxr =:qxr and t.czt=:zt";
			Map<String, Object> dqlkt_yxsj_params = new HashMap<String, Object>();
			for (Tdqlkt tdqlkt : tdqlkts) {
				dqlkt_yxsj_params.put("zh", tdqlkt.getCzh());
				dqlkt_yxsj_params.put("qxr", tdqlkt.getCqxr());
				dqlkt_yxsj_params.put("zt", "正常");//如果不是正常的说明已经提前支取或者部提了，不再重复计算
				Tyxsj dqlkt_tyxsj = yxsjDao.get(dqlkt_yxsj_hql, dqlkt_yxsj_params);// 查询营销数据中是否有该提支账号
				if (dqlkt_tyxsj != null) {
					// 如果存在该提支账号，判断是部提还是全部提前支取
					String glzh = tdqlkt.getCglzh();
					if (!StringUtils.isEmpty(glzh)) {// 如果有关联账号，则可能属于部提
						Tdqlkt dqlkt = dqlktDao.get(Tdqlkt.class, glzh);
						while (dqlkt != null && !StringUtils.isEmpty(dqlkt.getCglzh())) {// 做循环查找，防止一个月多次部提，找到最终的没有关联账号的
							glzh = dqlkt.getCglzh();
							dqlkt = dqlktDao.get(Tdqlkt.class, glzh);
						}
						if (dqlkt.getCzt() == 0) {// 最终账号不是销户状态，则是部提
							Ttqsj dqlkt_ttqsj = addBtTqsj(tqsj, dqlkt_tyxsj, 2, dqlkt);
							if (dqlkt_ttqsj != null && !findTqsj(dqlkt_ttqsj)) {
								dqlkt_tyxsj.setCzt("部分提前支取");
								dqlkt_tyxsj.setCmodifydate(modifyDate);
								dqlkt_tyxsj.setTuserByCmodifyuserid(createUser);
								dqlkt_ttqsj.setCtqrq(tdqlkt.getCxhr());//设置销户日为提前支取日期
								tqsjDao.save(dqlkt_ttqsj);
								count++;
							}

						} else {// 最终账号是销户状态，则是全部提前支取
							Ttqsj dqlkt_ttqsj = addAllTqsj(tqsj, dqlkt_tyxsj, 2, tdqlkt);// 添加到提前支取表
							if (!findTqsj(dqlkt_ttqsj)) {
								dqlkt_tyxsj.setCzt("全部提前支取");
								dqlkt_tyxsj.setCmodifydate(modifyDate);
								dqlkt_tyxsj.setTuserByCmodifyuserid(createUser);
								tqsjDao.save(dqlkt_ttqsj);
								count++;
							}
						}
					} else {// 没有关联账号则是全部提前支取，写入提前支取表中
						Ttqsj dqlkt_ttqsj = addAllTqsj(tqsj, dqlkt_tyxsj, 2, tdqlkt);// 添加到提前支取表
						if (!findTqsj(dqlkt_ttqsj)) {
							dqlkt_tyxsj.setCzt("全部提前支取");
							dqlkt_tyxsj.setCmodifydate(modifyDate);
							dqlkt_tyxsj.setTuserByCmodifyuserid(createUser);
							tqsjDao.save(dqlkt_ttqsj);
							count++;
						}
					}
				}
			}

		}
		// 计算一本通提前支取
		String dqobk_hql = " from Tdqobk t where t.czt=1 and cast(t.cxhr as int) < cast(t.cdqr as int) and substring(t.cxhr,1,6) =:tqyf and t.ckhj in(:wdids)";
		Map<String, Object> dqobk_params = new HashMap<String, Object>();
		dqobk_params.put("tqyf", tqyf.toString());
		dqobk_params.put("wdids", wdids);
		List<Tdqobk> tdqobks = dqobkDao.find(dqobk_hql, dqobk_params);// 获取所有一本通提前支取的账号
		if (tdqobks != null && tdqobks.size() > 0) {
			// 根据账号和起息日查找营销数据，因为有3个月的多次上报，所以要判断起息日
			String dqobk_yxsj_hql = " from Tyxsj t where t.czh =:zh and t.cqxr =:qxr and t.czt=:zt";
			Map<String, Object> dqobk_yxsj_params = new HashMap<String, Object>();
			for (Tdqobk tdqobk : tdqobks) {
				dqobk_yxsj_params.put("zh", tdqobk.getCzh());
				dqobk_yxsj_params.put("qxr", tdqobk.getCqxr());
				dqobk_yxsj_params.put("zt", "正常");//如果不是正常的说明已经提前支取或者部提了，不再重复计算
				Tyxsj dqobk_tyxsj = yxsjDao.get(dqobk_yxsj_hql, dqobk_yxsj_params);// 查询营销数据中是否有该提支账号
				if (dqobk_tyxsj != null) {
					// 如果存在该提支账号，判断是部提还是全部提前支取
					String glzh = tdqobk.getCglzh();
					if (!StringUtils.isEmpty(glzh)) {// 如果有关联账号，则可能属于部提
						Tdqobk dqobk = dqobkDao.get(Tdqobk.class, glzh);
						while (dqobk != null && !StringUtils.isEmpty(dqobk.getCglzh())) {// 做循环查找，防止一个月多次部提，找到最终的没有关联账号的
							glzh = dqobk.getCglzh();
							dqobk = dqobkDao.get(Tdqobk.class, glzh);
						}
						if (dqobk.getCzt() == 0) {// 最终账号不是销户状态，则是部提
							Ttqsj dqobk_ttqsj = addBtTqsj(tqsj, dqobk_tyxsj, 3, dqobk);
							if (dqobk_ttqsj != null && !findTqsj(dqobk_ttqsj)) {
								dqobk_ttqsj.setCtqrq(tdqobk.getCxhr());//设置销户日为提前支取日期
								dqobk_tyxsj.setCzt("部分提前支取");
								dqobk_tyxsj.setCmodifydate(modifyDate);
								dqobk_tyxsj.setTuserByCmodifyuserid(createUser);
								tqsjDao.save(dqobk_ttqsj);
								count++;
							}

						} else {// 最终账号是销户状态，则是全部提前支取
							Ttqsj dqobk_ttqsj = addAllTqsj(tqsj, dqobk_tyxsj, 3, tdqobk);// 添加到提前支取表
							if (!findTqsj(dqobk_ttqsj)) {
								dqobk_tyxsj.setCzt("全部提前支取");
								dqobk_tyxsj.setCmodifydate(modifyDate);
								dqobk_tyxsj.setTuserByCmodifyuserid(createUser);
								tqsjDao.save(dqobk_ttqsj);
								count++;
							}
						}
					} else {// 没有关联账号则是全部提前支取，写入提前支取表中
						Ttqsj dqobk_ttqsj = addAllTqsj(tqsj, dqobk_tyxsj, 3, tdqobk);// 添加到提前支取表
						if (!findTqsj(dqobk_ttqsj)) {
							dqobk_tyxsj.setCzt("全部提前支取");
							dqobk_tyxsj.setCmodifydate(modifyDate);
							dqobk_tyxsj.setTuserByCmodifyuserid(createUser);
							tqsjDao.save(dqobk_ttqsj);
							count++;
						}
					}
				}
			}

		}

		return count;
	}

	/**
	 * 如果是全部提前支取--添加到提前支取数据表中
	 * 
	 * @param tqsj
	 * @param tyxsj
	 * @param kindid
	 * @param dq
	 * @return
	 */
	private Ttqsj addAllTqsj(Tqsj tqsj, Tyxsj tyxsj, Integer kindid, Object dq) {
		Ttqsj ttqsj = new Ttqsj();
		switch (kindid) {
		case 1:
			Tdqcd tdqcd = (Tdqcd) dq;
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCtqrq(tdqcd.getCxhr());
			break;
		case 2:
			Tdqlkt tdqlkt = (Tdqlkt) dq;
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCtqrq(tdqlkt.getCxhr());
			break;
		case 3:
			Tdqobk tdqobk = (Tdqobk) dq;
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCtqrq(tdqobk.getCxhr());
			break;
		default:
			break;
		}
		return ttqsj;
	}

	/**
	 * 生成提前数据记录。tqsj--前台传过来的vo，tyxsj--发生提前支取的原账号营销记录，ttqsj--新生成的记录，--
	 * 
	 * @param tqsj
	 * @param tyxsj
	 * @param ttqsj
	 * @param tdqcd
	 */
	private void setTqsj(Tqsj tqsj, Tyxsj tyxsj, Ttqsj ttqsj) {
		BeanUtils.copyProperties(tyxsj, ttqsj, new String[] { "cid", "ccreatedate", "cmodifydate", "tuserByCcreateuserid", "tuserByCmodifyuserid" });
		ttqsj.setCid(UUID.randomUUID().toString());
		ttqsj.setCyjf(tyxsj.getCjf());
		ttqsj.setCtqje(tyxsj.getCye());
		ttqsj.setCtqjf(tyxsj.getCjf());
		ttqsj.setCtqfy(tyxsj.getCyxfy());
		ttqsj.setCzt("全部提前支取");
		Date d = new Date();
		ttqsj.setCcreatedate(new Timestamp(d.getTime()));
		ttqsj.setCmodifydate(new Timestamp(d.getTime()));
		if (!StringUtils.isEmpty(tqsj.getCreateuserid())) {
			ttqsj.setTuserByCcreateuserid(userDao.get(Tuser.class, tqsj.getCreateuserid()));
			ttqsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, tqsj.getCreateuserid()));
		}
	}

	/**
	 * 处理部提：1.将发生部提账号的zt设为“部提” 2.将部提后生成的新账号加入到营销数据中，营销费用设为0，统计营销费用总数时不区分zt标志. 3.计算提前支前后的营销奖励费用，计算提前金额，提前积分，将差值写入提前支取表中。
	 * 
	 * @param tyxsj
	 * @param kindid
	 * @param dq
	 */
	private Ttqsj addBtTqsj(Tqsj tqsj, Tyxsj tyxsj, Integer kindid, Object dq) {
		Ttqsj ttqsj = new Ttqsj();
		switch (kindid) {
		case 1:
			Tdqcd tdqcd = (Tdqcd) dq;// 获取定期数据，这里是关联账号的记录
			// 1.将部提后新账号加入yxsj表中
			Tyxsj yxsj = addBtYxsj(tqsj, tyxsj,1, tdqcd);
			Double yxjl = getYxjl(yxsj);// 计算新账号的积分奖励
			yxsj.setCyxfy(0.0);// 部提后新账号不算营销费用
			if (!findYxsj(yxsj)){//判断是否存在，防止重复添加
				yxsjDao.save(yxsj);
			}// 添加新账号结束

			// 2.将老账号添加到提前支取Ttqsj表中
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCzt("部分提前支取");
			ttqsj.setCtqje(tyxsj.getCye() - yxsj.getCye());
			ttqsj.setCtqjf(tyxsj.getCjf() - yxsj.getCjf());
			ttqsj.setCtqfy(DoubleUtil.Keep2(tyxsj.getCyxfy() - yxjl*yxsj.getCjf()));
			return ttqsj;
		case 2:
			Tdqlkt dqlkt = (Tdqlkt) dq;// 获取定期数据
			// 1.将部提后新账号加入yxsj表中
			Tyxsj dqlkt_yxsj = addBtYxsj(tqsj, tyxsj, 2,dqlkt);
			Double dqlkt_yxjl = getYxjl(dqlkt_yxsj);// 计算新账号的积分奖励
			dqlkt_yxsj.setCyxfy(0.0);// 部提后新账号不算营销费用
			if (!findYxsj(dqlkt_yxsj)){//判断是否存在，防止重复添加
				yxsjDao.save(dqlkt_yxsj);
			}// 添加新账号结束

			// 2.将老账号添加到提前支取Ttqsj表中
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCzt("部分提前支取");
			ttqsj.setCtqje(tyxsj.getCye() - dqlkt_yxsj.getCye());
			ttqsj.setCtqjf(tyxsj.getCjf() - dqlkt_yxsj.getCjf());
			ttqsj.setCtqfy(DoubleUtil.Keep2(tyxsj.getCyxfy() - dqlkt_yxjl*dqlkt_yxsj.getCjf()));
			return ttqsj;
		case 3:
			Tdqobk tdqobk = (Tdqobk) dq;// 获取定期数据
			// 1.将部提后新账号加入yxsj表中
			Tyxsj dqobk_yxsj = addBtYxsj(tqsj, tyxsj, 3,tdqobk);
			Double dqobk_yxjl = getYxjl(dqobk_yxsj);// 计算新账号的积分奖励
			dqobk_yxsj.setCyxfy(0.0);// 部提后新账号不算营销费用
			if (!findYxsj(dqobk_yxsj)){//判断是否存在，防止重复添加
				yxsjDao.save(dqobk_yxsj);
			}// 添加新账号结束

			// 2.将老账号添加到提前支取Ttqsj表中
			setTqsj(tqsj, tyxsj, ttqsj);
			ttqsj.setCzt("部分提前支取");
			ttqsj.setCtqje(tyxsj.getCye() - dqobk_yxsj.getCye());
			ttqsj.setCtqjf(tyxsj.getCjf() - dqobk_yxsj.getCjf());
			ttqsj.setCtqfy(DoubleUtil.Keep2(tyxsj.getCyxfy() - dqobk_yxjl*dqobk_yxsj.getCjf()));
			return ttqsj;
		default:
			break;
		}
		return null;
	}

	/**
	 * 将部提后生成的新账号加入营销数据表中
	 * 
	 * @param tqsj
	 * @param tyxsj
	 * @param tdqcd
	 * @return
	 */
	private Tyxsj addBtYxsj(Tqsj tqsj, Tyxsj tyxsj, Integer kindid, Object dq) {
		Tyxsj yxsj = new Tyxsj();// 添加新账号开始
		switch (kindid) {
		case 1:
			Tdqcd tdqcd = (Tdqcd) dq;
			BeanUtils.copyProperties(tdqcd, yxsj, new String[] { "czt" });
			yxsj.setCbz(tdqcd.getCzh() + "部提后新账号");
			break;
		case 2:
			Tdqlkt tdqlkt = (Tdqlkt) dq;
			BeanUtils.copyProperties(tdqlkt, yxsj, new String[] { "czt" });
			yxsj.setCbz(tdqlkt.getCkh() + "部提后新账号");
			break;
		case 3:
			Tdqobk tdqobk = (Tdqobk) dq;
			BeanUtils.copyProperties(tdqobk, yxsj, new String[] { "czt" });
			yxsj.setCkh(tdqobk.getCybt());
			yxsj.setCbz(tdqobk.getCybt() + "部提后新账号");
			break;
		default:
			break;
		}
		yxsj.setCid(UUID.randomUUID().toString());
		yxsj.setCbh(tyxsj.getCbh());
		yxsj.setTdept(tyxsj.getTdept());
		yxsj.setTkind(tyxsj.getTkind());
		yxsj.setCsbyf(tyxsj.getCsbyf());
		yxsj.setCzt("正常");
		yxsj.setTuserByCuserid(tyxsj.getTuserByCuserid());
		if (!StringUtils.isEmpty(tqsj.getCreateuserid())) {
			yxsj.setTuserByCcreateuserid(userDao.get(Tuser.class, tqsj.getCreateuserid()));
			yxsj.setTuserByCmodifyuserid(userDao.get(Tuser.class, tqsj.getCreateuserid()));
		}
		Date d = new Date();
		yxsj.setCcreatedate(new Timestamp(d.getTime()));
		yxsj.setCmodifydate(new Timestamp(d.getTime()));
		Double jf = yeToJf(yxsj);// 换算积分
		yxsj.setCjf(jf);
		return yxsj;
	}

	/**
	 * 根据单位的id查找出下属的所有网点的机构号集合
	 */
	private List<String> listJghByDeptid(String deptid) {
		List<String> jghs = new ArrayList<String>();
		String hql = "from Twd t where t.tdept.cid in(:ids)";
		Map<String, Object> params = new HashMap<String, Object>();
		List<String> ids = getAllChildTdeptIds(deptid);// 获取所有的下属单位id
		params.put("ids", ids);
		List<Twd> twds = wdDao.find(hql, params);
		if (twds != null && twds.size() > 0) {
			for (Twd t : twds) {
				jghs.add(t.getCjgh());
			}
		}
		return jghs;
	}

	/**
	 * 根据一个单位的id，获取它本身以及下属的所有的单位id集合
	 * 
	 * @param deptid
	 * @param depts
	 * @return
	 */
	private List<Tdept> getAllChildTdepts(String deptid, List<Tdept> depts) {
		Tdept dept = deptDao.get(Tdept.class, deptid);
		depts.add(dept);
		Set<Tdept> tdepts = dept.getTdepts();
		List<Tdept> list = new ArrayList<Tdept>(tdepts);
		if (list != null && list.size() > 0) {
			for (Tdept t : list) {
				getAllChildTdepts(t.getCid(), depts);
			}
		}
		return depts;
	}

	private List<String> getAllChildTdeptIds(String deptid) {
		List<String> ids = new ArrayList<String>();
		List<Tdept> depts = new ArrayList<Tdept>();
		depts = getAllChildTdepts(deptid, depts);
		for (Tdept t : depts) {
			ids.add(t.getCid());
		}
		return ids;
	}

	/**
	 * 通过起息日、单位、业务种类来得出积分的营销奖励
	 */
	private Double getYxjl(Tyxsj tyxsj) {
		// 查询规则：起息日必须大于等于开始日期，小于等于结束日期，金额要大于等于最小金额，小于最大金额，创建的单位id相等，业务种类也必须相等。
		String hql = " from Tyxjl t where t.cstartdate<=:qxr and t.cenddate>=:qxr and t.tdept.cid =:deptid ";
		hql += " and t.cmin<= :jf and t.cmax >:jf and t.tkind.cid =:kindid";
		Map<String, Object> params = new HashMap<String, Object>();
		// 获取单位上级单位的ID，用于查找营销奖励，营销奖励设置是由上级单位创建
		params.put("deptid", tyxsj.getTdept().getTdept().getCid());
		Timestamp qxr = new Timestamp(DateUtil.toDate(tyxsj.getCqxr()).getTime());
		params.put("qxr", qxr);
		params.put("jf", tyxsj.getCjf());
		params.put("kindid", tyxsj.getTkind().getCid());
		Tyxjl tyxjl = yxjlDao.get(hql, params);
		if (tyxjl != null) {
			return tyxjl.getCfee();
		}
		return 0.0;
	}

	/**
	 * 账号余额换算成积分
	 */
	private Double yeToJf(Tyxsj tyxsj) {
		String hql = "from Tjfsz t where t.tdept.cid =:deptid and t.tkind.cid =:kindid";
		Map<String, Object> params = new HashMap<String, Object>();
		// 获取单位上级单位的ID，用于查找积分规则，积分规则是由上级单位创建
		params.put("deptid", tyxsj.getTdept().getTdept().getCid());
		params.put("kindid", tyxsj.getTkind().getCid());
		Tjfsz tjfsz = jfszDao.get(hql, params);
		if (tjfsz != null) {
			Double cjf = tjfsz.getCjf();
			Double cje = tjfsz.getCje();
			if (tyxsj.getCcq() <= 12) {// 少于1年的除以12再乘存期
				if (tyxsj.getCcq() != 7) {
					return DoubleUtil.Keep2(tyxsj.getCye() * tyxsj.getCcq() * cjf / (12 * cje));
				} else {// 7天存款不算积分
					return 0.0;
				}
			} else {// 大于等于12个月的算1年
				return DoubleUtil.Keep2(tyxsj.getCye() * cjf / cje);
			}
		} else
			return 0.0;
	}

	/**
	 * 根据条件查询tqsj是否存在
	 * 
	 * @param bh
	 * @param zh
	 * @param sbyf
	 * @return
	 */
	private boolean findTqsj(Ttqsj t) {
		String hql = "from Ttqsj t where t.cbh = :bh and t.ctqrq =:tqrq and t.czh =:zh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("zh", t.getCzh());
		params.put("tqrq", t.getCtqrq());
		params.put("bh", t.getCbh());
		Ttqsj tqsj = tqsjDao.get(hql, params);
		if (tqsj != null) {
			return true;
		}
		return false;
	}
	
	/**
	 * 根据起息日和账号来判断该账号是否已经有人上报，否则已报 由于存在3个月的账号，一年内可能会多次上报，所以要加上起息日来判断
	 */
	private boolean findYxsj(Tyxsj yxsj) {
		String hql = " from Tyxsj t where  t.cqxr = :qxr  and t.czh =:zh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("qxr", yxsj.getCqxr());
		params.put("zh", yxsj.getCzh());
		Tyxsj t = yxsjDao.get(hql, params);
		if (t !=null){
			return true;
		}
		return false;
	}

	/**
	 * 按年份统计出个人营销总积分和完成任务的百分比
	 */
	@Override
	public DataGrid datagrid_tjgr(Tqsj tqsj) {
		DataGrid dg = new DataGrid();
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.ctqjf),sum(t.ctqfy)) from Ttqsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		hql += " group by t.cbh order by  t.cbh";
		List<YxsjTj> lists = tjDao.find(hql, params, tqsj.getPage(), tqsj.getRows());
		for (YxsjTj tj : lists) {
			Tuser tuser = getUserByBh(tj.getCbh());
			tj.setDeptname(tuser.getTdept().getCname());
			tj.setUsername(tuser.getCname());
		}
		dg.setRows(lists);
		dg.setTotal(total_tjgr(tqsj));
		return dg;
	}


	private Long total_tjgr(Tqsj tqsj){
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.ctqjf),sum(t.ctqfy)) from Ttqsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		hql += " group by t.cbh ";
		List<YxsjTj> lists = tjDao.find(hql, params);
		if (null != lists && lists.size() >0 ){
			return (long)lists.size();
		}
		return 0L;
	}
	
	/**
	 * 统计个人营销任务完成情况
	 */
	@Override
	public List<YxsjTj> excelData_tjgr(Tqsj tqsj) {
		String hql = "select new com.chinapost.vo.YxsjTj(t.cbh,sum(t.ctqjf),sum(t.ctqfy)) from Ttqsj t where 1=1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		hql = addWhere(tqsj, hql, params);
		hql += " group by t.cbh order by t.cbh";
		List<YxsjTj> tjs = tjDao.find(hql, params);
		// 将得到的list转为Map
		Map<String, YxsjTj> tjmap = new HashMap<String, YxsjTj>();
		for (YxsjTj tj : tjs) {
			tjmap.put(tj.getCbh(), tj);
		}
		String user_hql = "select new com.chinapost.vo.YxsjTj(t.tdept.cname,t.cbh,t.cname) from Tuser t where t.ckind=2 and  t.tdept.cid in (:deptids) ";
		Map<String, Object> user_params = new HashMap<String, Object>();
		if (!StringUtils.isEmpty(tqsj.getCbh())){
			user_hql += " and t.cbh =:bh ";
			user_params.put("bh", tqsj.getCbh());
		}
		user_hql += " order by t.tdept.cseq,t.cbh";
		user_params.put("deptids", tqsj.getDeptIds());
		List<YxsjTj> lists = tjDao.find(user_hql, user_params);
		if (lists != null && lists.size() > 0) {
			for (YxsjTj tj : lists) {
				YxsjTj yxsjtj = tjmap.get(tj.getCbh());
				if (yxsjtj != null) {// 如果员工有提前支取则设置，没有则设为0
					tj.setCzjf(yxsjtj.getCzjf());//czjf代表提前支取总积分
					tj.setCjh(yxsjtj.getCjh());//cjh代表提前支取扣款
				} else {
					tj.setCzjf(0.0);
					tj.setCjh(0.0);
				}
			}
		}
		return lists;
	}

	/**
	 * 通过编号查找用户
	 * 
	 * @param bh
	 * @return
	 */
	private Tuser getUserByBh(String bh) {
		String hql = "from Tuser t where t.cbh =:bh";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("bh", bh);
		return userDao.get(hql, params);
	}


}
